/****************************************************************************/
/*This program opens the infogroup data that was geocoded by a graduate student.*/
/*More recent versions of the data often have the block group already coded */
/****************************************************************************/
 
#delimit;
clear;
set matsize 800;
set more off;
capture log close;
set trace off;

/****************************************************************************/
/* DEFINE THE PROJECT FOLDER                                                */
/****************************************************************************/

local pathsmr  C:\Research;

/****************************************************************************/
/*DEFINE PATH TO FOLDERS                                                    */
/****************************************************************************/

local Torpath  `pathsmr'\Tornadoes;

/*******************************************/
/*Start Year Loop                          */
/*******************************************/

foreach i of numlist 1997(1)2017 { ;

/*******************************************/
/*Open first missing data set to append on */
/*******************************************/

if `i' < 2016 {;

clear;
insheet using "`Torpath'\Data\missing\business_level_`i'_result.csv";

if `i' >= 2006 & `i' < 2010 {;
   gen temp1 = substr(block_fips,1,15);
   replace block_fips = temp1 if length(block_fips) == 16;
   drop temp1;
};

capture destring block_fips, replace;

capture drop secondary_zip_code longitude;

save `Torpath'\Temp\temp_`i'_missing.dta, replace;

/*******************************************/
/*Open second missing data set to append on*/
/*******************************************/

clear;
insheet using "`Torpath'\Data\missing2\business_level_`i'_missing2_cty_geo_result.csv";

rename blk_id block_fips;

gen missing2 =1;

capture drop secondary_zip_code longitude;

save `Torpath'\Temp\temp_`i'_missing2.dta, replace;

/*******************************************/
/*Open third missing data set to append on */
/*******************************************/

clear;
insheet using "`Torpath'\Data\missing3\business_level_`i'_missing3_cty_geo_result.csv";

rename blk_id block_fips;

gen missing2 =3;

capture drop secondary_zip_code longitude;
save `Torpath'\Temp\temp_`i'_missing3.dta, replace;

};

/*******************************************/
/*Open main data set                       */
/*******************************************/

clear;
insheet using "`Torpath'\Data\Business_with_bgid\business_level_`i'_long_result.csv";
display "1";

if `i' < 2016 {;

capture drop secondary_zip_code;

append using `Torpath'\Temp\temp_`i'_missing.dta;
display "2";
append using `Torpath'\Temp\temp_`i'_missing2.dta;
display "3";
append using `Torpath'\Temp\temp_`i'_missing3.dta;
display "4";

};

/****************************************************************************/
/*Clean the raw data                                                        */
/****************************************************************************/

if `i' >= 2016 {;
   rename blk_id block_fips;
};

drop if block_fips ==.;

gen str15 bgfips = string(block_fips, "%15.0f");

gen sic2code_temp = string(sic2code);
replace sic2code_temp = "0" + sic2code_temp if length(sic2code_temp) == 1;
drop sic2code;
rename sic2code_temp sic2code;

if `i' >= 2015 {; 
capture rename employeesize5location empsdt;
capture rename salesvolume9location slsvdt;
};

/*Temporaryly save the data*/

sort bgfips sic2code;
save `Torpath'\Data\Business_with_bgid\business_level_`i'_long_program3_clean.dta, replace;
save `Torpath'\Temp\temp.dta, replace;

/*******************************************/
/*Create All data                          */
/*******************************************/


collapse (sum) num_bus empsdt slsvdt, by(bgfips sic2code); 

rename num_bus firm;
rename empsdt  emp;
rename slsvdt  sales;

sort bgfips sic2code;
save `Torpath'\Temp\temp_all.dta, replace;

/*******************************************/
/*Create new data                          */
/*******************************************/

use `Torpath'\Temp\temp.dta, clear;

if `i' < 2001 {;
local yr_m1 = `i' - 1;
keep if year == `yr_m1';
};

if `i' >= 2001 {;
   keep if year == `i';
};

collapse (sum) num_bus empsdt slsvdt, by(bgfips sic2code); 

rename num_bus firm_1y;
rename empsdt  emp_1y;
rename slsvdt  sales_1y;

sort bgfips sic2code;
save `Torpath'\Temp\temp_new.dta, replace;

/*******************************************/
/*Create 2to3 data                         */
/*******************************************/

use `Torpath'\Temp\temp.dta, clear;

if `i' < 2001 {;
local yr_m2 = `i' - 2;
local yr_m3 = `i' - 3;
keep if year == `yr_m2' | year == `yr_m3';
};

if `i' >= 2001 {;
   local yr_m1 = `i' - 1;
   local yr_m2 = `i' - 2;
   keep if year == `yr_m1' | year == `yr_m2';
};

collapse (sum) num_bus empsdt slsvdt, by(bgfips sic2code); 

rename num_bus firm_2to3y;
rename empsdt  emp_2to3y;
rename slsvdt  sales_2to3y;

sort bgfips sic2code;
save `Torpath'\Temp\temp_2to3.dta, replace;

/*******************************************/
/*Create 4+ data                           */
/*******************************************/

use `Torpath'\Temp\temp.dta, clear;

if `i' < 2001 {;
local yr_m4 = `i' - 4;
keep if year <= `yr_m4' ;
};

if `i' >= 2001 {;
   local yr_m3 = `i' - 3;
   keep if year <= `yr_m3' ;
};

collapse (sum) num_bus empsdt slsvdt, by(bgfips  sic2code); 

rename num_bus firm_4plusy;
rename empsdt  emp_4plusy;
rename slsvdt  sales_4plusy;

sort bgfips sic2code;
save `Torpath'\Temp\temp_4plus.dta, replace;

/****************************************************************************/
/*Merge all business types together                                         */
/****************************************************************************/

use `Torpath'\Temp\temp_all.dta, clear;

sort bgfips sic2code;
merge 1:1 bgfips sic2code using `Torpath'\Temp\temp_new.dta;
tab _merge;
drop _merge;

sort bgfips sic2code;
merge 1:1 bgfips sic2code using `Torpath'\Temp\temp_2to3.dta;
tab _merge;
drop _merge;

sort bgfips sic2code;
merge 1:1 bgfips sic2code using `Torpath'\Temp\temp_4plus.dta;
tab _merge;
drop _merge;


save `Torpath'\Temp\bgfips_level_`i'_long.dta, replace;

/****************************************************************************/
/*Create 1 digit industries                                                 */
/****************************************************************************/

local varlist1 firm emp sales firm_1y emp_1y sales_1y firm_2to3y emp_2to3y sales_2to3y firm_4plusy emp_4plusy sales_4plusy;

/*All industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "all";

save `Torpath'\Temp\temp_`i'_all.dta, replace; 

/*manuf industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "20" | sic2code == "21" | sic2code == "22" | sic2code == "23" | sic2code == "24" | sic2code == "25" | sic2code == "26" | sic2code == "27" | sic2code == "28" | sic2code == "29" | sic2code == "30" | sic2code == "31" | sic2code == "32" | sic2code == "33" | sic2code == "34" | sic2code == "35" | sic2code == "36" | sic2code == "37" | sic2code == "38" | sic2code == "39";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "manuf";

save `Torpath'\Temp\temp_`i'_manuf.dta, replace; 

/*retail industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "52" | sic2code == "53" | sic2code == "54" | sic2code == "55" | sic2code == "56" | sic2code == "57" | sic2code == "58" | sic2code == "59";

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "retail";

save `Torpath'\Temp\temp_`i'_retail.dta, replace; 

/*service industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "70" | sic2code == "72" | sic2code == "73" | sic2code == "75" | sic2code == "76" | sic2code == "78" | sic2code == "79" | sic2code == "80" | sic2code == "81" | sic2code == "82" | sic2code == "83" | sic2code == "84" | sic2code == "86" | sic2code == "87" | sic2code == "89";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "service";

save `Torpath'\Temp\temp_`i'_service.dta, replace; 

/*trans industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "40" | sic2code == "41" | sic2code == "42" | sic2code == "43" | sic2code == "44" | sic2code == "45" | sic2code == "46" | sic2code == "47" | sic2code == "48" | sic2code == "49";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "trans";

save `Torpath'\Temp\temp_`i'_trans.dta, replace; 

/*agric industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "01" | sic2code == "02" | sic2code == "07" | sic2code == "08" | sic2code == "09";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "agric";

save `Torpath'\Temp\temp_`i'_agric.dta, replace; 

/*mining industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "12" | sic2code == "13" | sic2code == "14";

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "mining";

save `Torpath'\Temp\temp_`i'_mining.dta, replace; 

/*const industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "15" | sic2code == "16" | sic2code == "17";

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "const";

save `Torpath'\Temp\temp_`i'_const.dta, replace; 

/*whole industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "51" | sic2code == "52";

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "whole";

save `Torpath'\Temp\temp_`i'_whole.dta, replace; 

/*fire industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "60" | sic2code == "61" | sic2code == "62" | sic2code == "63" | sic2code == "64" | sic2code == "65" | sic2code == "67";

collapse (sum) `varlist1' , by(bgfips);
gen sic2code = "fire";

save `Torpath'\Temp\temp_`i'_fire.dta, replace; 

/*nonmanuf industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

drop if sic2code == "20" | sic2code == "21" | sic2code == "22" | sic2code == "23" | sic2code == "24" | sic2code == "25" | sic2code == "26" | sic2code == "27" | sic2code == "28" | sic2code == "29" | sic2code == "30" | sic2code == "31" | sic2code == "32" | sic2code == "33" | sic2code == "34" | sic2code == "35" | sic2code == "36" | sic2code == "37" | sic2code == "38" | sic2code == "39";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "nonmanuf";

save `Torpath'\Temp\temp_`i'_nonmanuf.dta, replace; 

/*nongov industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

drop if sic2code == "90" | sic2code == "91" | sic2code == "92" | sic2code == "93" | sic2code == "94" | sic2code == "95" | sic2code == "96" | sic2code == "97";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "nongov";

save `Torpath'\Temp\temp_`i'_nongov.dta, replace; 

/*noncngv industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

drop if sic2code == "90" | sic2code == "91" | sic2code == "92" | sic2code == "93" | sic2code == "94" | sic2code == "95" | sic2code == "96" | sic2code == "97" | sic2code == "15" | sic2code == "16" | sic2code == "17";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "noncngv";

save `Torpath'\Temp\temp_`i'_noncngv.dta, replace; 

/*public industries*/
use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

keep if sic2code == "90" | sic2code == "91" | sic2code == "92" | sic2code == "93" | sic2code == "94" | sic2code == "95" | sic2code == "96" | sic2code == "97";

collapse (sum) `varlist1', by(bgfips);
gen sic2code = "public";

save `Torpath'\Temp\temp_`i'_public.dta, replace; 


/*********************************************/
/*append all 1 digit industries to data      */
/*********************************************/

use `Torpath'\Temp\bgfips_level_`i'_long.dta, clear;

append using `Torpath'\Temp\temp_`i'_all.dta;
append using `Torpath'\Temp\temp_`i'_manuf.dta;
append using `Torpath'\Temp\temp_`i'_retail.dta;
append using `Torpath'\Temp\temp_`i'_service.dta;
append using `Torpath'\Temp\temp_`i'_trans.dta;
append using `Torpath'\Temp\temp_`i'_agric.dta;
append using `Torpath'\Temp\temp_`i'_mining.dta;
append using `Torpath'\Temp\temp_`i'_const.dta;
append using `Torpath'\Temp\temp_`i'_whole.dta;
append using `Torpath'\Temp\temp_`i'_fire.dta;
append using `Torpath'\Temp\temp_`i'_nonmanuf.dta;
append using `Torpath'\Temp\temp_`i'_nongov.dta;
append using `Torpath'\Temp\temp_`i'_noncngv.dta;
append using `Torpath'\Temp\temp_`i'_public.dta;

gen year = `i';

/****************************************************************************/
/*Save data and move to the next year                                       */
/****************************************************************************/


if `i' == 1997 {;
save `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_long_final.dta, replace;
};

if `i' >  1997 {;
append using `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_long_final.dta;
save `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_long_final.dta, replace; 
};


};  /*Close year Loop*/


/****************************************************************************/
/****************************************************************************/
/*Create data to be between a long and a wide version of the data set       */
/*This isn't creating a truely wide panel only in the sense of industry     */
/****************************************************************************/
/****************************************************************************/

global outcomestubs "firm emp sales firm_1y emp_1y sales_1y firm_2to3y emp_2to3y sales_2to3y firm_4plusy emp_4plusy sales_4plusy" ;

global industrystubs "all manuf retail service nonmanuf nongov noncngv public mining const trans whole agric fire" ;

foreach j of global industrystubs { ;

use `Torpath'\0515Miles_CountyM\Data\bgfips_2000-10_level_allyears_long_final.dta, clear;

keep if sic2code == "`j'";

foreach i of global outcomestubs { ;
rename `i' `i'_`j';

/*Advance to the next outcome*/
};

drop sic2code;

sort bgfips year;

display "sic is `j'";

if "`j'" == "all"  {;
save `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_wide_final.dta, replace;
};

if "`j'" ~= "all"  {;
merge 1:1 bgfips year using `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_wide_final.dta;
tab _merge;

drop _merge;

sort bgfips;
save `Torpath'\0515Miles_CountyM\Data\bgfips_level_allyears_wide_final.dta, replace; 
};

/*Advance to the next industry*/
};


log close;



